package com.ukefu.util.task;

import static org.elasticsearch.index.query.QueryBuilders.termQuery;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.springframework.data.domain.PageImpl;

import com.google.common.collect.ArrayListMultimap;
import com.ukefu.core.UKDataContext;
import com.ukefu.util.UKTools;
import com.ukefu.util.es.SearchTools;
import com.ukefu.util.es.UKDataBean;
import com.ukefu.util.extra.DataExchangeInterface;
import com.ukefu.webim.service.repository.ExtentionRepository;
import com.ukefu.webim.service.repository.JobDetailRepository;
import com.ukefu.webim.service.repository.ReporterRepository;
import com.ukefu.webim.service.repository.SysDicRepository;
import com.ukefu.webim.web.model.Contacts;
import com.ukefu.webim.web.model.Extention;
import com.ukefu.webim.web.model.JobDetail;
import com.ukefu.webim.web.model.MetadataTable;
import com.ukefu.webim.web.model.SysDic;
import com.ukefu.webim.web.model.TableProperties;

public class ExcelImportProecess extends DataProcess{
	private DecimalFormat format = new DecimalFormat("###");
	private AtomicInteger pages = new AtomicInteger() , errors = new AtomicInteger(); 
	
	public ExcelImportProecess(DSDataEvent event){
		super(event);
	}
	
	@Override
	public void process() {
		processExcel(event);
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes"})
	private void processExcel(final DSDataEvent event){
		InputStream is = null;  
    	try {
    		event.getDSData().getReport().setTableid(event.getDSData().getTask().getId());
    		if(event.getDSData().getUser()!=null){
    			event.getDSData().getReport().setUserid(event.getDSData().getUser().getId());
    			event.getDSData().getReport().setUsername(event.getDSData().getUser().getUsername());
    		}
    		
            try {  
                is = new FileInputStream(event.getDSData().getFile());  
            } catch (FileNotFoundException ex) {  
                ex.printStackTrace();
            }  
            boolean isExcel2003 = true;  
            if (isExcel2007(event.getDSData().getFile().getName())) {  
                isExcel2003 = false;  
            }
            
            Workbook wb = null;  
            try {  
                wb = isExcel2003 ? new HSSFWorkbook(is) : new XSSFWorkbook(is);  
            } catch (IOException ex) {  
                ex.printStackTrace();
            }  
            long start = System.currentTimeMillis() ;
            if(wb!=null) {
	            Sheet sheet = wb.getSheetAt(0);  
	            Row titleRow = sheet.getRow(0);
	            Row valueRow = sheet.getRow(1);
	            int totalRows = sheet.getPhysicalNumberOfRows(); 
	            int colNum = titleRow.getPhysicalNumberOfCells();
	            for(int i=2 ; i<totalRows && valueRow == null ; i++){
	            	valueRow = sheet.getRow(i);
	            	if(valueRow !=null){
	            		break ;
	            	}
	            }
	            /**
	             * 需要检查Mapping 是否存在
	             */
	            
	            Map<Object, List> refValues = new HashMap<Object , List>() ;
	            MetadataTable table = event.getDSData().getTask() ;
	            for(TableProperties tp : table.getTableproperty()){
	            	if(tp.isReffk() && !StringUtils.isBlank(tp.getReftbid())){
	            		DataExchangeInterface exchange = (DataExchangeInterface) UKDataContext.getContext().getBean(tp.getReftbid()) ;
	            		refValues.put(tp.getFieldname().toLowerCase(), exchange.getListDataByIdAndOrgi(null, null, event.getOrgi())) ;
	            	}
	            }
	            
	            Map<Object, Object> mapRepeat = new HashMap<Object , Object>() ;
	            for(int i=1 ; i<totalRows; i++){
	            	Row row = sheet.getRow(i) ;
	            	Object data = null ;
	            	boolean savePass = true;
	            	if(row!=null && !isRowEmpty(row)){
						if(event.getDSData().getClazz() != null) {
							data = event.getDSData().getClazz().newInstance() ;
						}
						Map<Object, Object> values = new HashMap<Object , Object>() ;
						ArrayListMultimap<String, Object> multiValues = ArrayListMultimap.create();
						boolean skipDataVal = false; //跳过数据校验
						StringBuffer pkStr = new StringBuffer() , allStr = new StringBuffer();
						for(int col=0 ; col<colNum ; col++){
							if(savePass){
								
								Cell value = row.getCell(col) ;
								Cell title = titleRow.getCell(col) ;
								String titleValue = getValue(title) ;
								if(!StringUtils.isBlank(titleValue)) {
									titleValue = titleValue.trim() ;
								}
								TableProperties tableProperties = getTableProperties(event, titleValue);
								if(tableProperties!=null && value!=null){
									String valuestr = getValue(value) ;
									
									if(!StringUtils.isBlank(valuestr)) {
										
										if(tableProperties.isSreplace() && !StringUtils.isBlank(tableProperties.getCurstr())) {
											valuestr = valuestr.replaceAll(tableProperties.getCurstr(), !StringUtils.isBlank(tableProperties.getTarstr()) ? tableProperties.getTarstr() : "") ;
										}
										if(tableProperties.isModits()){
											if(!StringUtils.isBlank(valuestr)) {
												multiValues.put(tableProperties.getFieldname().toLowerCase(), valuestr) ;
											}
										}else{
											if(tableProperties.isPrivatefield() && !tableProperties.isSysfield()){
												values.put("pri_"+tableProperties.getFieldname().toLowerCase(), valuestr) ;
												valuestr = UKTools.md5(valuestr);
											}
											if(tableProperties.isSeldata()){
												//启用字典项字段，特殊处理
												SysDicRepository sysDicRes =  UKDataContext.getContext().getBean(SysDicRepository.class);
												if(!StringUtils.isBlank(tableProperties.getSeldatacode())){
													SysDic sysDic = sysDicRes.findByCode(tableProperties.getSeldatacode());
													if(sysDic != null){
														List<SysDic> resultList = sysDicRes.findByDicidAndNameLike(sysDic.getId(), valuestr);
														if(!resultList.isEmpty()){
															values.put(tableProperties.getFieldname().toLowerCase(), resultList.get(0).getId()) ;
														}else{
															values.put(tableProperties.getFieldname().toLowerCase(), valuestr) ;
														}
													}else{
														values.put(tableProperties.getFieldname().toLowerCase(), valuestr) ;
													}
												}else{
													values.put(tableProperties.getFieldname().toLowerCase(), valuestr) ;
												}
											}else if(tableProperties.isReffk() && refValues.get(tableProperties.getFieldname().toLowerCase())!=null){
												List keys = refValues.get(tableProperties.getFieldname().toLowerCase()) ;
												if(keys != null) {
													values.put(tableProperties.getFieldname().toLowerCase() , getRefid(tableProperties,refValues.get(tableProperties.getFieldname().toLowerCase()) , valuestr)) ;
												}
											}else{
												values.put(tableProperties.getFieldname().toLowerCase(), valuestr) ;
											}
											if(tableProperties.isPk() && !tableProperties.getFieldname().toLowerCase().equalsIgnoreCase("id")){
												pkStr.append(valuestr) ;
												if((mapRepeat.get(tableProperties.getFieldname().toLowerCase()) != null && !mapRepeat.get(tableProperties.getFieldname().toLowerCase()).toString().contains(valuestr)) || (mapRepeat.get(tableProperties.getFieldname().toLowerCase()) == null)){
													boolean pass = true;
													StringBuffer strb = new StringBuffer();
													EntityManager entityManager = UKDataContext.getContext().getBean(EntityManager.class);
													strb.append("SELECT * FROM ").append(tableProperties.getTablename().toLowerCase()).append(" WHERE ") ;
													if("uk_user".equals(tableProperties.getTablename().toLowerCase())){
														strb.append("datastatus = false and ");
													}
													strb.append(tableProperties.getFieldname().toLowerCase()).append(" = ").append("'").append(valuestr).append("'");
													Query query = entityManager.createNativeQuery(strb.toString()) ;
													List resultList2 = query.getResultList();
													if(!resultList2.isEmpty()){
														pass = false;
													}
													if(pass){
														if(mapRepeat.get(tableProperties.getFieldname().toLowerCase()) != null){
															mapRepeat.put(tableProperties.getFieldname().toLowerCase(), tableProperties.getFieldname().toLowerCase().toString()+","+valuestr);
														}else{
															mapRepeat.put(tableProperties.getFieldname().toLowerCase(), valuestr);
														}
														savePass = true;
													}else{
														savePass = false;
													}
												}else{
													savePass = false;
													continue;
												}
											}
										}
										allStr.append(valuestr) ;
									}
									event.getDSData().getReport().setBytes(event.getDSData().getReport().getBytes() + valuestr.length());
									event.getDSData().getReport().getAtompages().incrementAndGet() ;
								}
							
							}else{
								break;
							}
							
						}
						
						String validFaildMessage = null ;
						if(savePass){
							values.put("orgi", event.getOrgi()) ;
							if(values.get("id") == null){
								if(pkStr.length() > 0) {
									values.put("id", UKTools.md5(pkStr.append(event.getDSData().getTask().getTablename()).toString())) ;
								}else {
									values.put("id", UKTools.md5(allStr.append(event.getDSData().getTask().getTablename()).toString())) ;
								}
							}
							if(event.getValues()!=null && event.getValues().size() > 0){
								values.putAll(event.getValues());
							}
							values.putAll(multiValues.asMap());
							for(TableProperties tp : table.getTableproperty()){
								if(!StringUtils.isBlank(tp.getDefaultvaluetitle())) {
									String valuestr = (String) values.get(tp.getFieldname().toLowerCase()) ;
									if(tp.getDefaultvaluetitle().indexOf("required") >= 0 && StringUtils.isBlank(valuestr)) {
										skipDataVal = true ; validFaildMessage = "required" ;break ;
									}else if(valuestr!=null && (tp.getDefaultvaluetitle().indexOf("numstr") >= 0 && !valuestr.matches("[\\d]{1,}"))) {
										skipDataVal = true ; validFaildMessage = "numstr" ;break ;
									}else if(valuestr!=null && (tp.getDefaultvaluetitle().indexOf("datenum") >= 0 || tp.getDefaultvaluetitle().indexOf("datetime") >= 0 )) {
										if(!valuestr.matches("[\\d]{4,4}-[\\d]{2,2}-[\\d]{2,2}") && !valuestr.matches("[\\d]{4,4}-[\\d]{2,2}-[\\d]{2} [\\d]{2,2}:[\\d]{2,2}:[\\d]{2,2}")) {
											skipDataVal = true ; validFaildMessage = "datenum" ; break ;
										}else {
											if(valuestr.matches("[\\d]{4,4}-[\\d]{2,2}-{1,1}")) {
												if("date".equals(tp.getDefaultfieldvalue())) {
													values.put(tp.getFieldname().toLowerCase(),UKTools.simpleDateFormat.parse(valuestr));
												}else {
													values.put(tp.getFieldname().toLowerCase(),UKTools.simpleDateFormat.format(UKTools.simpleDateFormat.parse(valuestr)));
												}
											}else if(valuestr.matches("[\\d]{4,4}-[\\d]{2,2}-[\\d]{2,2} [\\d]{2,2}:[\\d]{2,2}:[\\d]{2,2}")) {
												if("date".equals(tp.getDefaultfieldvalue())) {
													values.put(tp.getFieldname().toLowerCase(),UKTools.dateFormate.parse(valuestr));
												}else {
													values.put(tp.getFieldname().toLowerCase(),UKTools.simpleDateFormat.format(UKTools.dateFormate.parse(valuestr)));
												}
												
											}
										}
									}
								}
								//联系人-获得时间，导入时表格中有值则用表格中的，否则置为当前时间
								if("touchtime".equals(tp.getFieldname()) && !StringUtils.isBlank((String)values.get("touchtime"))){
									String valuestr = (String)values.get("touchtime");
									if(valuestr.matches("[\\d]{4,4}-[\\d]{2,2}-[\\d]{2,2}")){
										values.put("touchtime",UKTools.dateFormate.format(UKTools.dateFormate.parse(valuestr)));
									}else if(valuestr.matches("[\\d]{4,4}-[\\d]{2,2}-[\\d]{2,2} [\\d]{2,2}:[\\d]{2,2}:[\\d]{2,2}")){
										values.put("touchtime",UKTools.dateFormate.format(UKTools.dateFormate.parse(valuestr)));
									}else{
										values.put("touchtime",UKTools.dateFormate.format(new Date()));
									}
								}else if("touchtime".equals(tp.getFieldname())){
									values.put("touchtime",UKTools.dateFormate.format(new Date()));
								}
								if(tp.isReffk() && !StringUtils.isBlank(tp.getReftbid()) && refValues.get(tp.getFieldname().toLowerCase()) == null){
									DataExchangeInterface exchange = (DataExchangeInterface) UKDataContext.getContext().getBean(tp.getReftbid()) ;
									exchange.process(data, event.getOrgi());
								}
								//用户表特殊处理
								if(!StringUtils.isBlank(tp.getTablename()) && "uk_user".equals(tp.getTablename())){
									//分机号处理
									if("extno".equals(tp.getFieldname().toLowerCase()) && values.get("extno") != null){
										values.put("extno",values.get("extno").toString());
										ExtentionRepository extentionRes = UKDataContext.getContext().getBean(ExtentionRepository.class);
										List<Extention> extentionList = extentionRes.findByExtentionAndOrgi(values.get("extno").toString(), values.get("orgi").toString());
										if(!extentionList.isEmpty()){
											Extention extention = extentionList.get(0);
											
											extention.setUserid(values.get("id").toString());
											extention.setUsername(values.get("username").toString());
											extention.setUname(values.get("uname").toString());
											
											extentionRes.save(extention) ;
											
											values.put("hostid",extention.getHostid());
											values.put("extid",extention.getId());
											values.put("bindext",1);
											
										}
									}
									if("password".equals(tp.getFieldname().toLowerCase()) && values.get("password") != null){
										values.put("password", UKTools.md5(values.get("password").toString() ));
										
									}
								}
								
							}
							
							if(!values.containsKey("orgi")) {
								skipDataVal = true ;
							}
							event.getDSData().getReport().setTotal(pages.intValue());
							values.put("creater", event.getValues().get("creater")) ;
							if (!StringUtils.isBlank(event.getTablename()) && event.getTablename().equals(UKDataContext.EsTable.UK_CONTACTS.toString())) {
								if (values.get("itemid")!=null) {
									String id = UKTools.md5(pkStr.append(values.get("itemid")).append(event.getTablename()).toString());
									if (!StringUtils.isBlank(id)) {
										DataExchangeInterface exchange = (DataExchangeInterface) UKDataContext.getContext().getBean("contacts") ;
										Contacts contacts = (Contacts) exchange.getDataByIdAndOrgi(id, values.get("orgi").toString());
										if (contacts == null) {
											values.put("id", id) ;
										}else {
											if (contacts.isDatastatus()) {
												values.put("id", id) ;
											}else {
												continue;
											}
										}
									}
								}
							}
							
							
							if (!StringUtils.isBlank(table.getTablename()) && table.getTablename().equals("uk_blacklist")) {
								if(values.get("phone")!=null && values.get("channel").toString().equals(UKDataContext.ChannelTypeEnum.PHONE.toString())){
									values.put("id", UKTools.md5(values.get("phone").toString())) ;
								}
							}
						}
						
						
						if(data!=null && skipDataVal == false) {
							UKTools.populate(data, values);
							pages.incrementAndGet() ;
							if (data instanceof Contacts) {
								data = (Contacts)data ;
								if (!StringUtils.isBlank(((Contacts) data).getCusname())) {
									((Contacts) data).setCtype(UKDataContext.ContactsItemType.CUSTOMER.toString());
								}else {
									((Contacts) data).setCtype(UKDataContext.ContactsItemType.CONTACTS.toString());
								}
							}
							if(savePass){
								event.getDSData().getProcess().process(data);
							}
						}else if(data == null){
							/**
							 * 导入的数据，只写入ES
							 */
							if(skipDataVal == true) {	//跳过
								values.put("status", "invalid") ;
								values.put("validresult", "invalid") ;
								values.put("validmessage", validFaildMessage!=null ? validFaildMessage : "") ;
							}else {
								values.put("validresult", "valid") ;
							}
							values.put("status", UKDataContext.NamesDisStatusType.NOT.toString()) ;
							values.put("batid", event.getBatid()) ;
							
							values.put("createtime", System.currentTimeMillis()) ;
							values.put("callstatus", UKDataContext.NameStatusTypeEnum.NOTCALL.toString()) ;
							values.put("execid", event.getDSData().getReport().getId()) ;
							
							if(i%500 == 0) {
								UKDataContext.getContext().getBean(ReporterRepository.class).save(event.getDSData().getReport()) ;
							}
							
							if(values.get("cusid")==null) {
								values.put("cusid", values.get("id"))  ;
							}
							if (values.get("cusname") != null) {
								values.put("ctype", UKDataContext.ContactsItemType.CUSTOMER.toString()) ;
							}else {
								values.put("ctype", UKDataContext.ContactsItemType.CONTACTS.toString()) ;
							}
							pages.incrementAndGet() ;
							if(savePass){
								event.getDSData().getProcess().process(values);
							}
							/**
							 * 访客信息表
							 */
						}
						if(skipDataVal == true) {	//跳过
							errors.incrementAndGet();
							continue ;
						}
	            	}
				}
            }
            
            event.setTimes(System.currentTimeMillis() - start);
            event.getDSData().getReport().setEndtime(new Date());
            event.getDSData().getReport().setAmount(String.valueOf((float)event.getTimes()/1000f));
            event.getDSData().getReport().setStatus(UKDataContext.TaskStatusType.END.getType());
            event.getDSData().getReport().setTotal(pages.intValue());
            event.getDSData().getReport().setPages(pages.intValue());
            event.getDSData().getReport().setErrors(errors.intValue());
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(is!=null){
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if(event.getDSData().getFile().exists()){
				event.getDSData().getFile().delete() ;
			}
			event.getDSData().getProcess().end();
			/**
			 * 更新数据
			 */
			UKDataContext.getContext().getBean(ReporterRepository.class).save(event.getDSData().getReport()) ;
			if(event.getDSData().getClazz() == null && !StringUtils.isBlank(event.getBatid())) {
				JobDetailRepository batchRes = UKDataContext.getContext().getBean(JobDetailRepository.class) ;
				JobDetail batch = this.event.getDSData().getJobDetail();
				if(batch == null) {
					batch = batchRes.findByIdAndOrgi(event.getBatid(), event.getOrgi()) ;
				}
				if(batch!=null) {
					
					BoolQueryBuilder queryBuilder = new BoolQueryBuilder();
					queryBuilder.must(termQuery("orgi",batch.getOrgi()));
					queryBuilder.must(termQuery("batid",batch.getId()));
					PageImpl<UKDataBean> aggregationBatchData = SearchTools.aggregationBatchData(queryBuilder, true, 0, 1, "batid");
					if(aggregationBatchData != null && aggregationBatchData.getContent().size() > 0) {
						UKDataBean uKDataBean = aggregationBatchData.getContent().get(0);
						//总数
						if(uKDataBean.getValues().get("total") != null) {
							batch.setNamenum(Integer.parseInt(uKDataBean.getValues().get("total").toString()));
						}else {
							batch.setNamenum(0);
						}
						//未分配
						if(uKDataBean.getValues().get("status.not") != null){
							batch.setNotassigned(Integer.parseInt(uKDataBean.getValues().get("status.not").toString()));
							//已分配
							batch.setAssigned(batch.getNamenum() - Integer.parseInt(uKDataBean.getValues().get("status.not").toString()));
						}else {
							batch.setNotassigned(0);
							//已分配
							batch.setAssigned(batch.getNamenum());
						}
						//有效
						if(uKDataBean.getValues().get("validresult.valid") != null) {
							batch.setValidnum(Integer.parseInt(uKDataBean.getValues().get("validresult.valid").toString()));
						}else {
							batch.setValidnum(0);
						}
						//无效
						if(uKDataBean.getValues().get("validresult.invalid") != null) {
							batch.setInvalidnum(Integer.parseInt(uKDataBean.getValues().get("validresult.invalid").toString()));
						}else {
							batch.setInvalidnum(0);
						}
						
					}
					batchRes.save(batch) ;
				}
			}
		}
	}
	
	private String getRefid(TableProperties tp , List<Object> dataList , String value) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
		String id = "" ;
		for(Object data : dataList){
			Object target = null ;
			if(PropertyUtils.isReadable(data, "name")){
				target = BeanUtils.getProperty(data, "name") ;
				if(target!=null && target.equals(value)){
					id = BeanUtils.getProperty(data, "id") ;
				}
			}
			if(PropertyUtils.isReadable(data, "tag")){
				target = BeanUtils.getProperty(data, "tag") ;
				if(target!=null && target.equals(value)){
					id = BeanUtils.getProperty(data, "id") ;
				}
			}
			if(StringUtils.isBlank(id) && PropertyUtils.isReadable(data, "title")){
				target = BeanUtils.getProperty(data, "title") ; 
				if(target!=null && target.equals(value)){
					id = BeanUtils.getProperty(data, "id") ;
				}
			}
			if(StringUtils.isBlank(id)){
				target = BeanUtils.getProperty(data, "id") ; 
				if(target!=null && target.equals(value)){
					id = target.toString() ;
				}
			}
		}
		return id ;
	}
	
	private TableProperties getTableProperties(DSDataEvent event , String title){
		TableProperties tableProperties = null ; 
		for(TableProperties tp : event.getDSData().getTask().getTableproperty()){
			if(tp.getName().equalsIgnoreCase(title) || tp.getFieldname().equalsIgnoreCase(title)){
				tableProperties = tp ; break ;
			}
		}
		return tableProperties;
	}
	
	//判断空行
	@SuppressWarnings("deprecation")
	private static boolean isRowEmpty(Row row) {
		for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
			Cell cell = row.getCell(c);
			if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
				return false;
		}
		return true;
	}
	private boolean isExcel2007(String fileName) {  
        return fileName.matches("^.+\\.(?i)(xlsx)$");  
    } 
	@SuppressWarnings("deprecation")
	private String getValue(Cell cell){
		String strCell = "";
		if(cell!=null){
			short dt = cell.getCellStyle().getDataFormat() ;
	        switch (cell.getCellType()) {
		        case HSSFCell.CELL_TYPE_STRING:
		            strCell = cell.getStringCellValue();
		            break;
		        case HSSFCell.CELL_TYPE_BOOLEAN:
		            strCell = String.valueOf(cell.getBooleanCellValue());
		            break;
		        case HSSFCell.CELL_TYPE_BLANK:
		            strCell = "";
		            break;
		        case HSSFCell.CELL_TYPE_NUMERIC:
		        	if (HSSFDateUtil.isCellDateFormatted(cell)) {
		        		SimpleDateFormat sdf = null;  
		                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
		                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
		                } else {// 日期  
		                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
		                }  
		                strCell = sdf.format(cell.getDateCellValue());  
		        	} else if (cell.getCellStyle().getDataFormat() == 58) {  
		                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
		                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
		                double value = cell.getNumericCellValue();  
		                strCell = sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value));  
		            }else{
		            	
		            	if (HSSFDateUtil.isCellDateFormatted(cell)) {
		            		SimpleDateFormat sdf = null;  
			                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
			                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
			                } else {// 日期  
			                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
			                }  
			                strCell = sdf.format(cell.getDateCellValue());  
			        	}else{
			            	boolean isNumber = isNumberFormat(dt) ;
			        		if(isNumber){
			        			DecimalFormat numberFormat = getNumberFormat(cell.getCellStyle().getDataFormatString()) ;
			        			if(numberFormat!=null){
			        				strCell = String.valueOf(numberFormat.format(cell.getNumericCellValue()));
			        			}else{
			        				strCell = String.valueOf(cell.getNumericCellValue());
			        			}
			        		}else{
			        			strCell = String.valueOf(format.format(cell.getNumericCellValue())) ;
			        		}
			        	}
	                }
		            break;
		        case HSSFCell.CELL_TYPE_FORMULA: {
	                // 判断当前的cell是否为Date
		        	boolean isNumber = isNumberFormat(dt) ;
		        	try{
		        		if(isNumber){
		        			strCell = String.valueOf(cell.getNumericCellValue());
			        	}else{
			        		strCell = "";
			        	}
		        	}catch(Exception ex){
		        		ex.printStackTrace();
		        		strCell = cell.getRichStringCellValue().getString();  
		        	}
	                break;
	            }
		        default:
		            strCell = "";
		            break;
	        }
	        if (strCell.equals("") || strCell == null) {
	            return "";
	        }
		}
        return strCell;
	}
	
	@SuppressWarnings({ "deprecation", "unused" })
	private String getDataType(Cell cell){
		String dataType = "string";
		if(cell!=null){
			short dt = cell.getCellStyle().getDataFormat() ;
	        switch (cell.getCellType()) {
		        case HSSFCell.CELL_TYPE_STRING:
		        	dataType = "string";
		            break;
		        case HSSFCell.CELL_TYPE_BOOLEAN:
		        	dataType = "number";
		            break;
		        case HSSFCell.CELL_TYPE_BLANK:
		        	if (HSSFDateUtil.isCellDateFormatted(cell)) {
		        		if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
		        			dataType = "datetime" ;  
		                } else {// 日期  
		                	dataType = "datetime" ;  
		                }  
		        		
		        	} else if (cell.getCellStyle().getDataFormat() == 58){
		        		dataType = "datetime" ;
		        	}else{
		        		boolean isNumber = isNumberFormat(dt) ;
		        		if(isNumber){
		        			dataType = "number";
		        		}else{
		        			dataType = "string";
		        		}
		        	}
		            break;
		        case HSSFCell.CELL_TYPE_NUMERIC:
		        	if (HSSFDateUtil.isCellDateFormatted(cell)) {
		        		if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
		        			dataType = "datetime" ;  
		                } else {// 日期  
		                	dataType = "datetime" ;  
		                }  
		        		
		        	} else if (cell.getCellStyle().getDataFormat() == 58){
		        		dataType = "datetime" ;
		        	}else{
		        		if (HSSFDateUtil.isCellDateFormatted(cell)) {
			        		if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
			        			dataType = "datetime" ;  
			                } else {// 日期  
			                	dataType = "datetime" ;  
			                }  
			        	}else{
			        		boolean isNumber = isNumberFormat(dt) ;
			        		if(isNumber){
			        			dataType = "number";
			        		}else{
			        			dataType = "string";
			        		}
			        	}
	                }
		            break;
		        case HSSFCell.CELL_TYPE_FORMULA: {
	                // 判断当前的cell是否为Date
		        	boolean isNumber = isNumberFormat(dt) ;
	        		if(isNumber){
	        			dataType = "number";
	        		}else{
	        			dataType = "string";
	        		}
	                break;
	            }
		        default:
		        	dataType = "string";
		            break;
	        }
	       
		}
        return dataType;
	}
	
	private DecimalFormat getNumberFormat(String dataformat){
		DecimalFormat numberFormat = null ;
		int index = dataformat.indexOf("_") > 0 ?  dataformat.indexOf("_") : dataformat.indexOf(";") ;
		if(index > 0){
			String format = dataformat.substring( 0 , index) ;
			if(format.matches("[\\d.]{1,}")){
				numberFormat = new DecimalFormat(format);
			}
		}
		
		return numberFormat ;
	}
	
	private boolean isNumberFormat(short dataType){
		boolean number = false ;
		switch(dataType){
			case 180 : number = true  ; break; 
			case 181 : number = true  ; break; 
			case 182 : number = true  ; break;
			case 178 : number = true  ; break;
			case 177 : number = true  ; break;
			case 176 : number = true  ; break;
			case 183 : number = true  ; break; 
			case 185 : number = true  ; break; 
			case 186 : number = true  ; break;
			case 179 : number = true  ; break;
			case 187 : number = true  ; break; 
			case 7 : number = true  ; break; 
			case 8 : number = true  ; break; 
			case 44 : number = true  ; break; 
			case 10 : number = true  ; break; 
			case 12 : number = true  ; break; 
			case 13 : number = true  ; break; 
			case 188 : number = true  ; break; 
			case 189 : number = true  ; break; 
			case 190 : number = true  ; break; 
			case 191 : number = true  ; break; 
			case 192 : number = true  ; break; 
			case 193 : number = true  ; break; 
			case 194 : number = true  ; break; 
			case 11 : number = true  ; break; 

		}
		return number ;
	}


}
